﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Zgq.CommonTools
{
    public partial class C5DataBaseHelper
    {
        /// <summary>
        /// 数据库操作
        /// </summary>
        //public static IC5SqlDB SqlDBObject { get; set; } = new MSSqlParameterDB();
        public static IC5SqlDB SqlDBObject
        {
            get { return new MSSqlParameterDB(); }
            set { }
        }

        /// <summary>
        /// 获取对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public static List<T> SelectListStatic<T>(string sysDatabaseName, string whereStr = null, string orderByStr = null)
        {
            var list = GetModelListStatic<T>(sysDatabaseName, SqlDBObject.SelectList<T>(sysDatabaseName, whereStr, orderByStr));
            return list;
        }

        /// <summary>
        /// 获取子对象数据
        /// </summary>
        /// <typeparam name="MasterT"></typeparam>
        /// <typeparam name="ChildT"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="masterModel"></param>
        /// <returns></returns>
        public static List<ChildT> GetChildListStatic<MasterT, ChildT>(string sysDatabaseName, MasterT masterModel) where ChildT : class
        {
            //查询子集合
            if (masterModel != null)
            {
                var masterKeys = typeof(MasterT).GetPrimaryKeysByType();
                if (masterKeys == null || masterKeys.Count() == 0)
                    throw new Exception("无法获取到对象主键");

                StringBuilder sbwhere = new StringBuilder();
                StringBuilder sborder = new StringBuilder();
                bool isHaskey = false;

                var childKeys = typeof(ChildT).GetPrimaryKeysByType();
                if (childKeys == null || childKeys.Count() == 0)
                    throw new Exception("无法获取到对象主键");

                var childType = typeof(ChildT);
                sbwhere.Clear();
                isHaskey = false;
                foreach (var mkey in masterKeys)
                {
                    if (childKeys.FirstOrDefault(x => x == mkey) == null)
                        continue;

                    sbwhere.AppendFormat(" {0} = {1} and", mkey, masterModel.GetType().GetProperty(mkey).GetValue(masterModel, null));
                    sborder.AppendFormat(" {0} asc,", mkey);
                    isHaskey = true;
                }

                if (!isHaskey)
                    throw new Exception("无法获取到主子表主键关联信息");

                Type locType = typeof(C5DataBaseHelper);
                MethodInfo mi = locType.GetMethod("SelectList", new Type[] { typeof(string), typeof(string), typeof(string), typeof(List<object>) });
                MethodInfo dmi = mi.MakeGenericMethod(childType);
                var result = dmi.Invoke(locType, new object[] { sysDatabaseName, sbwhere.ToString().Remove(sbwhere.Length - 3, 3), sborder.ToString().Trim(','), null });
                return result as List<ChildT>;
            }

            return null;
        }

        /// <summary>
        /// 获取分页对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public static List<T> SelectListPagingStatic<T>(string sysDatabaseName, int pageIndex, int pageSize, string whereStr = null, string orderByStr = null)
        {
            return GetModelListStatic<T>(sysDatabaseName, SqlDBObject.SelectListPaging<T>(pageIndex, pageSize, sysDatabaseName, whereStr, orderByStr));
        }

        /// <summary>
        /// 获取分页对象集合 只查询部分列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public List<T> SelectListPagingColumnStatic<T>(int pageIndex, int pageSize, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            return GetModelList<T>(SqlDBObject.SelectListPagingColumn<T>(pageIndex, pageSize, sysDatabaseName, whereStr, orderByStr, colums));
        }

        /// <summary>
        /// 获取对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public static T SelectModelStatic<T>(string sysDatabaseName, string whereStr = null)
        {
            return GetModelStatic<T>(sysDatabaseName, SqlDBObject.SelectModel<T>(sysDatabaseName, whereStr));
        }

        /// <summary>
        /// 查询总量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public static int SelectModelCountStatic<T>(string sysDatabaseName, string whereStr = null)
        {
            return ExecuteScalarStatic(sysDatabaseName, SqlDBObject.SelectModelCount<T>(sysDatabaseName, whereStr)).ToInt();
        }

        /// <summary>
        /// 查询DataTable数据
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="tableName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public static DataTable SelectDatatableStatic(string sysDatabaseName, string tableName, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            return GetDataTableStatic(sysDatabaseName, SqlDBObject.SelectDatatable(tableName, sysDatabaseName, whereStr, orderByStr, colums));
        }

        /// <summary>
        /// 查询Datatable启用分页
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="tableName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public static DataTable SelectDatatablePagingStatic(string sysDatabaseName, int pageIndex, int pageSize, string tableName, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            return GetDataTableStatic(sysDatabaseName, SqlDBObject.SelectDatatablePaging(tableName, pageIndex, pageSize, sysDatabaseName, whereStr, orderByStr, colums));
        }

        /// <summary>
        /// 查询Datatable总量
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="tableName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public static int SelectDatatableCountStatic(string sysDatabaseName, string tableName, string whereStr = null)
        {
            return ExecuteScalarStatic(sysDatabaseName, SqlDBObject.SelectDatatableCount(tableName, sysDatabaseName, whereStr), null).ToInt();
        }

        /// <summary>
        /// 获取视图对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public static List<T> SelectViewModelListStatic<T>(string sysDatabaseName, string whereStr = null, string orderByStr = null)
        {
            //查询主表数据
            var masterList = GetModelListStatic<T>(sysDatabaseName, SqlDBObject.GetViewListSql<T>(whereStr, orderByStr));
            if (masterList == null || masterList.Count == 0) return masterList;

            //查询子表数据
            new C5DataBaseHelper().GetChildList(masterList);

            return masterList;
        }

        /// <summary>
        /// 获取视图分页对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public static List<T> SelectViewModelListPagingStatic<T>(string sysDatabaseName, int pageIndex, int pageSize, string whereStr = null, string orderByStr = null)
        {
            //查询主表数据
            var masterList = GetModelListStatic<T>(sysDatabaseName, SqlDBObject.GetViewListPagingSql<T>(pageIndex, pageSize, whereStr, orderByStr));
            if (masterList == null || masterList.Count == 0) return masterList;

            //查询子表数据
            new C5DataBaseHelper().GetChildList(masterList);

            return masterList;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int InsertStatic(string sysDatabaseName, object model)
        {
            List<object> sqlParameters = new List<object>();
            return ExecteNonQueryStatic(sysDatabaseName, SqlDBObject.GetInsertSql(model, sysDatabaseName, sqlParameters), CommandType.Text, sqlParameters.Cast<SqlParameter>().ToArray());
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int UpdateStatic(string sysDatabaseName, object model)
        {
            List<object> sqlParameters = new List<object>();
            return ExecteNonQueryStatic(sysDatabaseName, SqlDBObject.GetUpdateSql(model, sysDatabaseName, sqlParameters), CommandType.Text, sqlParameters.Cast<SqlParameter>().ToArray());
        }

        /// <summary>
        /// 删除集合
        /// </summary>
        /// <param name="sysDatabaseName">帐套名称</param>
        /// <param name="lists">删除数组</param>
        /// <returns></returns>
        public static int DeleteListStatic(string sysDatabaseName, params object[] lists)
        {
            List<object> sqlParameters = new List<object>();
            return ExecteNonQueryStatic(sysDatabaseName, SqlDBObject.GetDeleteListSql(lists, sysDatabaseName, sqlParameters), CommandType.Text, sqlParameters.Cast<SqlParameter>().ToArray());
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int DeleteStatic(string sysDatabaseName, object model)
        {
            List<object> sqlParameters = new List<object>();
            return ExecteNonQueryStatic(sysDatabaseName, SqlDBObject.GetDeleteSql(model, sysDatabaseName, sqlParameters), CommandType.Text, sqlParameters.Cast<SqlParameter>().ToArray());
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="model"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public static bool IsExistsStatic(string sysDatabaseName, object model, string whereStr = null)
        {
            return ExistsStatic(sysDatabaseName, SqlDBObject.GetExistsSql(model, sysDatabaseName, whereStr));
        }

        /// <summary>
        /// 一个仅供Lock锁定的对像
        /// </summary>
        private static object asyncLock = new object();
        /// <summary>
        /// 取得自动编号
        /// </summary>
        /// <param name="Context"></param> 设定数据的关联
        /// <param name="entityName"></param> 设定数据表的名称
        /// <returns></returns>
        public static Int64 GetNumberStatic(C5DataBaseHelper sqlConn, string entityName)
        {
            lock (asyncLock)
            {
                SqlParameter numberParameter = new SqlParameter() { Value = 0, ParameterName = "@END_ID", Direction = System.Data.ParameterDirection.Output };
                sqlConn.ExecuteScalar("SYS_GET_TABLE_SEQUENCE", System.Data.CommandType.StoredProcedure,
                                      new SqlParameter() { Value = entityName, ParameterName = "TABLE_NAME" },
                                      numberParameter
                                     );

                return long.Parse(numberParameter.Value.ToString());


            }
        }

        public static void SetNumberStatic(C5DataBaseHelper sqlConn, object obj, string PropertyInfo)
        {
            Type type = obj.GetType();
            //获取类名
            String className = type.Name;
            lock (asyncLock)
            {
                SqlParameter numberParameter = new SqlParameter() { Value = 0, ParameterName = "@END_ID", Direction = System.Data.ParameterDirection.Output };
                sqlConn.ExecuteScalar("SYS_GET_TABLE_SEQUENCE", System.Data.CommandType.StoredProcedure,
                                      new SqlParameter() { Value = className, ParameterName = "TABLE_NAME" },
                                      numberParameter
                                     );

                //return long.Parse(numberParameter.Value.ToString());

                ReflectionHelper.SetPropertyVal(obj, PropertyInfo, numberParameter.Value);
            }
        }

        /// <summary>
        /// 执行语句返回第一个结果
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object ExecuteScalarStatic(string sysDatabaseName,  string cmdText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteScalar(GetDbConnString(sysDatabaseName),  cmdText, commandParameters);
        }

        /// <summary>
        /// 查询一个DataTable
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static DataTable GetDataTableStatic(string sysDatabaseName, string cmdText, params SqlParameter[] cmdParms)
        {
            return SqlHelper.GetTable(GetDbConnString(sysDatabaseName), cmdText, cmdParms);
        }

        /// <summary>
        /// 查询一个DataSet
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static DataSet GetDataSetStatic(string sysDatabaseName, string cmdText, params SqlParameter[] cmdParms)
        {
            return SqlHelper.ExecuteDataSetText(GetDbConnString(sysDatabaseName), cmdText, cmdParms);
        }

        /// <summary>
        /// 检查是否存在
        /// </summary>
        /// <param name="strSql">Sql语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>bool结果</returns>
        public static bool ExistsStatic(string sysDatabaseName, string strSql, params SqlParameter[] cmdParms)
        {
            int cmdresult = Convert.ToInt32(ExecuteScalarStatic(sysDatabaseName, strSql, cmdParms));
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 查询一个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static T GetModelStatic<T>(string sysDatabaseName, string cmdText)
        {
            return SqlHelper.ReaderToModel<T>(GetDbConnString(sysDatabaseName), cmdText);
        }

        /// <summary>
        /// 查询对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sysDatabaseName"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static List<T> GetModelListStatic<T>(string sysDatabaseName, string cmdText)
        {
            return SqlHelper.ReaderToList<T>(GetDbConnString(sysDatabaseName), cmdText);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sysDatabaseName"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static int ExecteNonQueryStatic(string sysDatabaseName, string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] cmdParms)
        {
            return SqlHelper.ExecteNonQuery(GetDbConnString(sysDatabaseName), cmdType, cmdText, cmdParms);
        }
    }
}
